﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Diagnostics;
using System.Linq;
using Gqq.CodeFirst.Palace;
using Gqq.Common.Helper;
using Gqq.Common.Helper.CodeFirstLog;

namespace Gqq.CodeFirst.ProcessPalace
{
    public class Part1To4
    {
        public static void DoTest()
        {
            //            DoTestWithLinq();
            //            TestConnection();
            //            TestComplexProperties();
            TestAttach3();
        }

        private static readonly UnicornsContext db = new UnicornsContext();

        #region Using DbContext in EF 4.1 Part 5: Working with Property

        #region 有关CurrentValue和OriginalValue的有关概念。

        public static void TestCurrentValueAndOriginalValue()
        {
            var un = db.Unicorns.Find(9);
            // 使用lamda表达式的方式。
            var value = db.Entry(un).Property(x => x.Name).CurrentValue;
            CodeLogManager.DLog.Debug("name:" + value);
            CodeLogManager.DLog.Debug("state is:" + db.Entry(un).State);
            db.Entry(un).Property(x => x.Name).CurrentValue = "Us Info";
            CodeLogManager.DLog.Debug("state is:" + db.Entry(un).State);

            //使用属性名的硬编码方式
            value = db.Entry(un).Property("Name").CurrentValue as string;
            CodeLogManager.DLog.Debug("name:" + value);
            CodeLogManager.DLog.Debug("state is:" + db.Entry(un).State);
            db.Entry(un).Property("Name").CurrentValue = "Zhu zhu4";
            CodeLogManager.DLog.Debug("state is:" + db.Entry(un).State);
            CodeLogManager.DLog.Debug(db.Entry(un).Property(x => x.Name).OriginalValue);
            db.SaveChanges();

            //调用完SaveChanges方法后，状态重新变为Unchanged。OriginalValue会变成保存后的Value
            CodeLogManager.DLog.Debug("state is:" + db.Entry(un).State);
            CodeLogManager.DLog.Debug(db.Entry(un).Property(x => x.Name).OriginalValue);

            // not mapped的属性也是可以通过这种方式来读取的，如果有set方法，也可以进行写入操作。
            var waiting = db.LadiesInWaiting.Find(20, "The EF Castle");
            var value2 = db.Entry(waiting).Property(x => x.Name).CurrentValue;
            CodeLogManager.DLog.Debug("LadiesInWaiting name:" + value2);

            //测试一下，理论上，这句话会抛异常的，因为Name属性根本没有set方法。
            //Cannot set value for property 'Name' on entity of type 'LadyInWaiting' because the property has no set accessor.
            //            db.Entry(waiting).Property(x => x.Name).CurrentValue = "Error";
            var unicorn = new Unicorn { Name = "Franky" };
            CodeLogManager.DLog.Debug(db.Entry(unicorn).Property("Name").CurrentValue);
            db.Entry(unicorn).Property("Name").CurrentValue = "Ustc";
            CodeLogManager.DLog.Debug(db.Entry(unicorn).Property("Name").CurrentValue);

            //对于original values，在not mapped和not tracked中的属性中是不可以的。
            //            CodeLogManager.DLog.Debug(db.Entry(unicorn).Property("Name").OriginalValue);
        }

        public static void TestIsModified()
        {

            var un = db.Unicorns.Find(9);
            var name = un.Name;
            CodeLogManager.DLog.Debug(db.Entry(un).Property(x => x.Name).IsModified);
            un.Name = "test2";
            CodeLogManager.DLog.Debug(db.Entry(un).Property(x => x.Name).IsModified);
            un.Name = name;
            CodeLogManager.DLog.Debug(db.Entry(un).Property(x => x.Name).IsModified);
            //It is not currently possible to reset an individual property to be not modified
            //after it has been marked as modified. 
            //This is something we plan to support in a future release.
            //db.Entry(un).Property(x => x.Name).IsModified = false;
            CodeLogManager.DLog.Debug(db.Entry(un).Property(x => x.Name).IsModified);

        }

        public static void GetCurrentWithDb()
        {
            var un = db.Unicorns.Find(9);
            un.Name = "Test";
            db.Database.ExecuteSqlCommand("update unicorn set `name`='Squeaky' where id=9");
            PrintDbProperties(db.Entry(un).CurrentValues);
            PrintDbProperties(db.Entry(un).OriginalValues);
            //DbPropertyValues，根据这个类型，可以解决很多问题。
            PrintDbProperties(db.Entry(un).GetDatabaseValues());

        }
        #endregion

        #region 利用Dictionary更新CurrentValue和OriginalValue
        public static void ModifyCurrentValueWithObject()
        {
            var princess = db.Princesses.Find(19);
            var rapunzel = new Princess { Id = 19, Name = "Rapunzel" };
            var rosannella = new PrincessDto { Id = 19, Name = "Rosannella" };

            //这样的话，状态变为unchanged
            //db.Princesses.Attach(rapunzel);
            //db.Entry(rapunzel).State = EntityState.Added;
            var entry = db.Entry(princess);
            PrintDbProperties(entry.CurrentValues);
            entry.CurrentValues.SetValues(rapunzel);
            entry.OriginalValues.SetValues(rosannella);
            PrintDbProperties(entry.CurrentValues);
            //其实，OriginalValues也是可以改变的哦
            PrintDbProperties(entry.OriginalValues);
        }

        /// <summary>
        /// 利用Dictionary更新CurrentValue和OriginalValue
        /// </summary>
        public static void ModifyCurrentValueWithProperty()
        {
            var casle = db.Castles.Find("The EF Castle");
            var entry = db.Entry(casle);
            var newValues = new Dictionary<string, object>
                                {
                                    {"Name", "The EF Castle"}, 
                                    {"Location.City", "Redmond"},
                                    {"Location.Kingdom", "Building 18"}, 
                                    {"Location.ImaginaryWorld.Name", "Magic Astoria World"}, 
                                    {"Location.ImaginaryWorld.Creator", "Ado.net"}
                                };
            foreach (KeyValuePair<string, object> pair in newValues)
            {
                entry.Property(pair.Key).CurrentValue = pair.Value;
            }
        }



        public static void ModifyCurrentValueWithEntry()
        {
            //利用dictionary修改currentValues，利用CurrentValues修改值。
            var lady = db.LadiesInWaiting.Find(19, "The EF Castle");
            Dictionary<string, object> ladies = new Dictionary<string, object> { { "FirstName", "Calypso" }, { "Title", "Prima donna" } };

            //得到lady的实体。
            DbEntityEntry<LadyInWaiting> entry = db.Entry(lady);
            foreach (KeyValuePair<string, object> lad in ladies)
            {
                entry.CurrentValues[lad.Key] = lad.Value;
            }
        }

        #endregion

        #region ComplexProperty的测试

        /// <summary>
        /// ComplexProperty和Property的属性有点像。
        /// ComplexProperty的返回值为DbComplexPropertyEntry
        /// Property的返回值为DbPropertyEntry
        /// 如果一个属性是Property而不是ComplexProperty，强制使用ComplexProperty会抛出异常。
        /// </summary>
        public static void GetComplexProperty()
        {
            var casle = db.Castles.Find("The EF Castle");
            var entry = db.Entry(casle);
            //The property 'Name' on type 'Castle' is not a complex property. 
            //The ComplexProperty method can only be used with complex properties. 
            //Use the Property, Reference or Collection method.
            //var name = entry.ComplexProperty(x => x.Name).CurrentValue;
#pragma warning disable 168
            var name = entry.Property(x => x.Name).CurrentValue;
            var name2 = entry.Property(x => x.Name).CurrentValue;
            //var city = entry.ComplexProperty(x => x.Location.City).CurrentValue;
            var city = entry.Property(x => x.Location.City).CurrentValue;
            var city2 = entry.Property("Location.City").CurrentValue;
            var city3 = entry.ComplexProperty(x => x.Location).Property(x => x.City).CurrentValue;
            var world1 = entry.ComplexProperty(x => x.Location).Property(y => y.ImaginaryWorld).CurrentValue;
            var world2 = entry.ComplexProperty(x => x.Location).ComplexProperty(y => y.ImaginaryWorld).CurrentValue;
            var creator = entry.ComplexProperty(x => x.Location).ComplexProperty(y => y.ImaginaryWorld).Property(x => x.Creator).CurrentValue;
#pragma warning restore 168
        }

        #endregion

        #region 获得数据库中的实体
        /// <summary>
        /// 获得数据库中的实体
        /// </summary>
        public static void TestDatabaseEntity()
        {
            //用这种办法可以处理并发性。
            //DbEntityEntry<TEntity>是一个很有用的类。可以通过主key找到相应的数据库。
            var unicron = db.Unicorns.Find(9);
#pragma warning disable 168
            var clonedUnicorn = db.Entry(unicron).GetDatabaseValues().ToObject();
#pragma warning restore 168
            //            GetAllProperties(string.Empty, db.Entry(unicron).CurrentValues);
        }

        #endregion

        #endregion

        #region Using DbContext in EF 4.1 Part 4: Add/Attach and Entity

        /// <summary>
        /// 测试Attach方法。
        /// Add、Attach方法不同点就是Add会把state变为Added，但是Attach后，会变为Unchanged
        /// blog:http://blogs.msdn.com/b/adonet/archive/2011/01/29/using-dbcontext-in-ef-feature-ctp5-part-4-add-attach-and-entity-states.aspx
        /// </summary>
        public static void TestAttach()
        {

            // 可以利用这种方式插入，插入后PrincessId=0
            //            Unicorn un = new Unicorn() {Name = "Franky"};
            // （1）第一种方式，附加
            //            db.Unicorns.Add(un);
            //            db.SaveChanges();

            //利用这种办法，可以把Id=9的Unicorn的PrincessId改成21。
            //            Unicorn un = db.Unicorns.SingleOrDefault(x => x.Id == 9);
            //            un.Princess = db.Princesses.Find(21);
            ////            db.SaveChanges();
            //
            //            un = new Unicorn() {Name = "Franky", PrincessId = 1};
            //            un.Princess = new Princess() {Name = "Victoria"};
            ////            db.Unicorns.Add(un);
            //            //（2）例外一种添加的方式。
            //            db.Entry<Unicorn>(un).State = EntityState.Added;
            #region 1、通过一个属性向另外一个属性中添加对象
            //            
            //            Unicorn un = db.Unicorns.SingleOrDefault(x => x.Id == 9);
            //            un.Princess = new Princess() {Name = "WenCheng"};
            //            //db.Unicorns.Add(un);
            //            db.Entry(un).State = EntityState.Modified;

            #endregion

            #region 2、通过一个属性向另外一个属性中添加对象
            //
            //            var princess = db.Princesses.Find(24);
            //            princess.Unicorns.Add(new Unicorn { Name = "Cat" });
            //            princess.Unicorns.Add(new Unicorn { Name = "Dog" });
            //            db.Entry(princess).State = EntityState.Modified;


            #endregion
            #region 3、测试Attach方法

            var uni = db.Princesses.Find(24).Unicorns.FirstOrDefault();
            //            // attach表示关联，把Unicorns中的一条数据通过attach到db中，并且进行了修改。
            db.Unicorns.Attach(uni);
            //在不关联的情况下直接改变其值。
            uni.Name = "Franky No";
            int result = db.SaveChanges();
            Console.WriteLine(result);


            #endregion

            #region 4、比较通用的插入删除方法

            InsertOrUpdateUnicorns(new Unicorn {Name = "Temp"});
            InsertOrUpdateUnicorns(db.Unicorns.FirstOrDefault(x => x.Name == "Dog"));

            #endregion
        }

        public static void TestAttach2()
        {
            //如果不进行一次数据库查询，直接使用Local，那么Local中没有数据。
            Console.WriteLine(db.Unicorns.Local.Count); // 0

            db.Unicorns.Where(x => x.Name.StartsWith("B")).Load();
            //执行以上命令后，Local中有两条数据。
            Console.WriteLine(db.Unicorns.Local.Count); // 2

            //然后，我们再Attach一条数据。
            var newUnicorn = new Unicorn() { Name = "Wangdu", PrincessId = 26 };
            //使用了Attach之后，db.Entry(unicorn).State的状态依然是EntityState.Unchanged。
            db.Unicorns.Attach(newUnicorn);
            //可以手动修改这条的状态。
            //db.Entry(newUnicorn).State = EntityState.Modified;

            foreach (Unicorn unicorn in db.Unicorns.Local)
            {
                Console.WriteLine(unicorn);
                Console.WriteLine(db.Entry(unicorn).State);
                Console.WriteLine();
            }

            //如果添加了db.Entry(newUnicorn).State = EntityState.Added;，则会增加一条数据。
            Console.WriteLine(db.SaveChanges());
        }

        /// <summary>
        /// 在Id相同的情况下，如果先使用Attach关联对象，再将状态改为Modified，
        /// 那么数据库中的字段就会发生改变。
        /// </summary>
        public static void TestAttach3()
        {
            var princess = new Princess {Id = 25, Name = "Rose"};
            db.Princesses.Attach(princess);
            db.Entry(princess).State = EntityState.Modified;
            db.SaveChanges();
        }

        public static void EditNavagateProperties()
        {
            // 1、通过以下办法，给unicorn付一个Princess。
            //var unicorn = db.Unicorns.Find(15);
            //unicorn.Princess = db.Princesses.Find(25);
            ////检查state，是否为Modified
            //Console.WriteLine(db.Entry(unicorn).State);
            //Console.WriteLine(db.SaveChanges());

            // 2、可以公国unicorn来创建一个新的princess吗？我测试一下。
            var unicorn = db.Unicorns.Find(15);
            unicorn.Princess = new Princess() {Name = "WenCheng"};
            Console.WriteLine(db.SaveChanges());//执行后，princess表中添加一条数据，并且id为15的unicorn的princessId变为
            //新添加的Id
        }
       
        #endregion

        #region Part 3: Finding Entities
        /// <summary>
        /// 1、如果在db中找不到这个主key，才会循环查询数据库。
        /// 2、如果db中新加入了对象但是没有插入数据库。查询也会返回“Added”对象的实体。
        /// </summary>
        public static void TestFind()
        {

            const int id = 21;

            // 这次会连接数据库。执行时间2099ms
            Stopwatch sw = new Stopwatch();
            sw.Start();
            Princess p = db.Princesses.Find(id);
            sw.Stop();
            Console.WriteLine("第一次执行时间：" + sw.ElapsedMilliseconds);
            if (p == null)
            {
                Console.WriteLine("没有找到");
                return;
            }
            //这次不会连接数据库。执行时间3ms
            sw.Restart();
#pragma warning disable 168
            Princess p2 = db.Princesses.Find(id);
            sw.Stop();
            Console.WriteLine("第二次执行时间：" + sw.ElapsedMilliseconds);

            db.Princesses.Add(new Princess { Id = 100 });
            var p3 = db.Princesses.Find(100);
            Console.WriteLine(p3 == null ? "新添加没有入库的数据无法查询到。" : "新添加没有入库的数据可以查询到。");

            var waiting = db.LadiesInWaiting.Find(20, "The EF Castle");
            // Console.WriteLine(waiting.CastleName);
            //                GetAllProperties(string.Empty,)
            // ReSharper disable RedundantTypeArgumentsOfMethod
            WriteProperties<LadyInWaiting>(waiting);
            // ReSharper restore RedundantTypeArgumentsOfMethod
#pragma warning restore 168
        }

        #endregion

        #region Part 2: Connections and Models
        public static void TestComplexProperties()
        {

            //var princess = db.Unicorns.FirstOrDefault();
            //DbPropertyValues values = db.Entry(princess).CurrentValues;
            //foreach (string propertyName in values.PropertyNames)
            //{
            //    Console.WriteLine(propertyName + ":");
            //    var property = values[propertyName] as DbPropertyValues;
            //    if (property == null)
            //    {
            //        Console.WriteLine(values[propertyName].GetType().FullName);
            //    }
            //    else
            //    {
            //        Console.WriteLine("This is DbPropertyValues");
            //    }
            //}



            var castle = db.Castles.Find("The EF Castle");
            if (castle == null)
            {
                CodeLogManager.DLog.Debug("没有找到相应的casle");
            }
            else
            {
                #region 1、第一种方法

                //                   castle.Location.City
                //                       castle.Location.Kingdom
                //                    castle.Location.ImaginaryWorld.Creator
                CodeLogManager.DLog.DebugFormat("城市：{0}，王国：{1}，建立者：{2}",
                                                castle.Location.City,
                                                castle.Location.Kingdom,
                                                castle.Location.ImaginaryWorld.Creator);

                #endregion

                #region 2、第二种方法

                // 也可以利用这种方法获取字段。（这是一种反射的方法）
                var city = db.Entry(castle).Property(x => x.Location.City).CurrentValue;
                var oldCity = db.Entry(castle).Property(x => x.Location.City).OriginalValue;

                CodeLogManager.DLog.DebugFormat("当今city：{0},以前city：{1}", city, oldCity);

                #endregion

                #region 3、第三种方法

                city = db.Entry(castle).ComplexProperty(x => x.Location).Property(x => x.City).CurrentValue;
                var kindom = db.Entry(castle).ComplexProperty(x => x.Location).Property(x => x.Kingdom).CurrentValue;
                CodeLogManager.DLog.DebugFormat("当今city：{0},以前kingdom：{1}", city, kindom);

                #endregion

                #region 4、第四种方法

                var location = db.Entry(castle).ComplexProperty(x => x.Location).CurrentValue;
                city = location.City;
                kindom = location.Kingdom;
                CodeLogManager.DLog.DebugFormat("当今city：{0},以前kingdom：{1}", city, kindom);

                #endregion

                //db.Entry(castle).Property[]
                PalaceCommon.GetAllProperties(string.Empty, db.Entry(castle).CurrentValues);
            }
        }

        #endregion

        #region Part 1: Introduction and Model
        /// <summary>
        /// 对于Dbset，我们也可以使用Linq查询
        /// </summary>
        public static void DoTestWithLinq()
        {
            //直接使用Linq进行查询
            var bUnicorn = from un in db.Unicorns
                           where un.Name.StartsWith("B")
                           select un;

            //bUnicorn.ToList().ForEach(Console.WriteLine);

            //SingleOrDefault和FirstOrDefault的差别在于SingleOrDefault如果查出来有两条，就会报异常。
            var princess = (from q in db.Princesses
                            where q.Name == "Sleeping Beauty"
                            orderby q.Id
                            select q).SingleOrDefault();
            //if (princess != null) Console.WriteLine(princess.Id);

            //查询ladyinwaiting
            var lady = from q in db.LadiesInWaiting
                       where q.PrincessId == 19 || q.PrincessId == 20
                       select q;
            foreach (LadyInWaiting ladyInWaiting in lady)
            {
                Console.WriteLine(ladyInWaiting.Name);
            }
        }

        #endregion

        #region Part 0：连接数据库字符串

        public static void TestConnection()
        {
            //因为构造函数中使用了“name=”，所以app.config中必须要有Unicorns2这个配置key
            //如果没有这个key，就会抛出异常来。
            //1、使用字符串的构造函数
            using (var db2 = new UnicornsContext("Unicorns2"))
            {
                //                db2.Unicorns.Load();
            }

            //2、可以使用DbConnection来
            var conn = new PrincessContext().Database.Connection;

            using (var db3 = new UnicornsContext(conn))
            {
                var firstOrDefault = db3.Unicorns.FirstOrDefault(x => x.Name.StartsWith("s"));
                if (firstOrDefault != null) Console.WriteLine(firstOrDefault.Name);
            }
        }

        #endregion


        #region 一些通常用的私有方法。


        private static void PrintDbProperties(DbPropertyValues currentValues)
        {
            foreach (var pn in currentValues.PropertyNames)
            {
                Console.WriteLine("{0}——{1}", pn, currentValues[pn]);
            }
        }



        /// <summary>
        /// 写出一个类值类型和string类型的所有字段。
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="obj"></param>
        private static void WriteProperties<T>(T obj)
        {
            obj.GetType().GetProperties().ToList().ForEach(x =>
            {
                CodeLogManager.DLog.Debug(x.PropertyType.Name);
                //特别注意是否为string类型的算法。
                if (x.PropertyType.IsValueType || x.PropertyType == typeof(string))
                    Console.WriteLine("{0}——{1}", x.Name, x.GetValue(obj, null));
            });
        }

        /// <summary>
        /// 可以通过这种方法进行插入或修改
        /// </summary>
        /// <param name="u"></param>
        /// <returns></returns>
        // ReSharper disable UnusedMember.Local
        private static int InsertOrUpdateUnicorns(Unicorn u)
        {
            db.Unicorns.Attach(u);
            db.Entry(u).State = u.Id == 0 ? EntityState.Added : EntityState.Modified;
            int n = db.SaveChanges();
            CodeLogManager.DLog.Debug(n);
            return n;
        }
        // ReSharper restore UnusedMember.Local

        #endregion
    }
}